﻿using System;
using System.Collections.Generic;
using System.Linq;
using System.Web;
using System.Web.UI;
using System.Web.UI.WebControls;
using NPOI.HSSF.UserModel;
using NPOI.HPSF;
using System.IO;
using System.Data;
using System.Data.SqlClient;
using System.Configuration;
namespace syc.Store.报表
{
    public partial class 物资库存明细表 : System.Web.UI.Page
    {
        protected HSSFWorkbook hssfworkbook;
        protected string year;
        protected string month;
        //成品
        protected double y2you = 0;//二分厂月油管本期入库
        protected double y5you = 0;//五分厂月油管本期入库
        protected double y1tao = 0;//一分厂月套管本期入库
        protected double y2tao = 0;//二分厂月套管本期入库

        protected double y2youc = 0;//二分厂月油管本期出库
        protected double y5youc = 0;//五分厂月油管本期出库
        protected double y1taoc = 0;//一分厂月套管本期出库
        protected double y2taoc = 0;//二分厂月套管本期出库     

        protected double 油管上期结存;
        protected double 套管上期结存;
        protected double 油管本期结存;
        protected double 套管本期结存;

        protected double y2youN80 = 0;//二分厂月油管本期入库
        protected double y5youN80 = 0;//五分厂月油管本期入库
        protected double y1taoN80 = 0;//一分厂月套管本期入库
        protected double y2taoN80 = 0;//二分厂月套管本期入库

        protected double y2youcN80 = 0;//二分厂月油管本期出库
        protected double y5youcN80 = 0;//五分厂月油管本期出库
        protected double y1taocN80 = 0;//一分厂月套管本期出库
        protected double y2taocN80 = 0;//二分厂月套管本期出库     

        protected double 油管N80上期结存;
        protected double 套管N80上期结存;
        protected double 油管N80本期结存;
        protected double 套管N80本期结存;

        //半成品

        //原料
        protected double 油管光管期初库存;
        protected double 套管光管期初库存;
        protected double 油管接箍光管期初库存;
        protected double 套管接箍光管期初库存;
        protected double 成品接箍期初库存;
        protected double 成品N80接箍期初库存;
        protected int 自产油管接箍期初库存;
        protected int 自产套管接箍期初库存;


        protected double 油管光管本期收入;
        protected double 套管光管本期收入;
        protected double 油管接箍光管本期收入;
        protected double 套管接箍光管本期收入;
        protected double 成品套管接箍本期收入;
        protected double 成品N80套管接箍本期收入;
        protected int 自产油管接箍本期收入;
        protected int 自产套管接箍本期收入;


        protected double 一分厂套管料本期发出;
        protected double 二分厂套管料本期发出;
        protected double 二分厂油管料本期发出;
        protected double 五分厂油管料本期发出;
        protected double 三分厂油管接箍料本期发出;
        protected double 三分厂套管接箍料本期发出;
        protected double 一分厂成品套管接箍本期发出 = 0;
        protected double 一分厂成品N80套管接箍本期发出;
        protected double 二分厂成品套管接箍本期发出 = 0;
        protected double 二分厂成品N80套管接箍本期发出 = 0;

        protected double 一分厂自产套管接箍本期发出;
        protected int 二分厂自产套管接箍本期发出;
        protected int 一分厂自产油管接箍本期发出;
        protected int 二分厂自产油管接箍本期发出;

        protected double 一分厂自产保护器本期发出;
        protected double 二分厂自产保护器本期发出;

        protected double 油管光管本期发出;
        protected double 套管光管本期发出;
        protected double 油管接箍光管本期发出;
        protected double 套管接箍光管本期发出;
        protected double 成品套管接箍本期发出;
        protected double 成品N80套管接箍本期发出;
        protected int 自产油管接箍本期发出;
        protected int 自产套管接箍本期发出;


        protected double 油管料本期结存;
        protected double 套管料本期结存;
        protected double 油管接箍料本期结存;
        protected double 套管接箍料本期结存;
        protected double 成品套管接箍本期结存;
        protected int 自产油管接箍本期结存;
        protected int 自产套管接箍本期结存;


        //材料
        //保护器
        protected double 聚乙烯期初库存;
        protected double 聚乙烯本期入库;
        protected double 聚乙烯本期出库;
        protected double 聚乙烯期末库存;

        protected double 钢带期初库存;
        protected double 钢带本期入库;
        protected double 钢带本期出库;
        protected double 钢带期末库存;

        protected double 套管螺纹保护环期初库存;
        protected double 套管螺纹保护环本期入库;
        protected double 套管螺纹保护环本期出库;
        protected double 套管螺纹保护环期末库存;

        protected double 油管螺纹保护环期初库存;
        protected double 油管螺纹保护环本期入库;
        protected double 油管螺纹保护环本期出库;
        protected double 油管螺纹保护环期末库存;

        protected double 自产螺纹保护环期初库存;
        protected double 自产螺纹保护环本期入库;
        protected double 自产螺纹保护环本期出库;
        protected double 自产螺纹保护环期末库存;
        //待处理
        protected double 待处理油管期末库存;
        protected double 待处理套管期末库存;
        protected double 待处理油管本期发出;
        protected double 待处理套管本期发出;
        protected double 一分厂待处理套管本期发出;
        protected double 二分厂待处理套管本期发出;     
        protected double 二分厂待处理油管本期发出;
        protected double 五分厂待处理油管本期发出;

        protected void Page_Load(object sender, EventArgs e)
        {
            if (!IsPostBack)
            {
                this.Year0.SelectedValue = DateTime.Today.Year.ToString();
                this.Month0.SelectedValue = DateTime.Today.Month.ToString();
                year = this.Year0.SelectedValue;
                month = this.Month0.SelectedValue;
            }
            Button_Query_Click(null, null);
        }

        protected void Year0_SelectedIndexChanged(object sender, EventArgs e)
        {

        }

        protected void Month0_SelectedIndexChanged(object sender, EventArgs e)
        {

        }

        protected void Button_Query_Click(object sender, EventArgs e)
        {
//            year = this.Year0.SelectedValue;
//            month = this.Month0.SelectedValue;
//            if (month == "1")
//            {
//                year = (int.Parse(year) - 1).ToString();
//                month = "12";
//            }
//            else
//            {
//                month = (int.Parse(month) - 1).ToString();
//            }
            SqlConnection Conn = new SqlConnection();
            Conn.ConnectionString = ConfigurationManager.ConnectionStrings["SCMConnectionString1"].ConnectionString;
            Conn.Open();
            SqlCommand Cmd = new SqlCommand();
            Cmd.Connection = Conn;
            //原料
            //期初库存-------------@lym表示上期月末-------------@lym表示上期月末库存
            string sql = @"            
            select dbo.原料期初库存(@年度,@月份,1),dbo.原料期初库存(@年度,@月份,2),dbo.原料期初库存(@年度,@月份,3),dbo.原料期初库存(@年度,@月份,4),dbo.原料期初库存(@年度,@月份,6),dbo.原料期初库存(@年度,@月份,106),dbo.自产接箍期初库存(@年度,@月份,1),dbo.自产接箍期初库存(@年度,@月份,2)";
            sql = sql.Replace("@年度", this.Year0.SelectedValue);
            sql = sql.Replace("@月份", this.Month0.SelectedValue);
            Cmd.CommandText = sql;
            SqlDataReader Reader = Cmd.ExecuteReader();
            if (Reader.Read())
            {
                油管光管期初库存 = Reader.GetDouble(0);
                套管光管期初库存 = Reader.GetDouble(1);
                油管接箍光管期初库存 = Reader.GetDouble(2);
                套管接箍光管期初库存 = Reader.GetDouble(3);
                成品接箍期初库存 = Reader.GetDouble(4);
                成品N80接箍期初库存 = Reader.GetDouble(5);
                自产油管接箍期初库存 = Reader.GetInt32(6);
                自产套管接箍期初库存 = Reader.GetInt32(7);
            }
            Reader.Close();

            //本期收入
            string sql1 = @"            
            select dbo.原料本期收入(@年度,@月份,1),dbo.原料本期收入(@年度,@月份,2),dbo.原料本期收入(@年度,@月份,3),dbo.原料本期收入(@年度,@月份,4),dbo.原料本期收入(@年度,@月份,6),dbo.原料本期收入(@年度,@月份,106),dbo.自产接箍本期收入(@年度,@月份,1),dbo.自产接箍本期收入(@年度,@月份,2)";
            sql1 = sql1.Replace("@年度", this.Year0.SelectedValue);
            sql1 = sql1.Replace("@月份", this.Month0.SelectedValue);
            Cmd.CommandText = sql1;
            Reader = Cmd.ExecuteReader();
            if (Reader.Read())
            {
                油管光管本期收入 = Reader.GetDouble(0);
                套管光管本期收入 = Reader.GetDouble(1);
                油管接箍光管本期收入 = Reader.GetDouble(2);
                套管接箍光管本期收入 = Reader.GetDouble(3);
                成品套管接箍本期收入 = Reader.GetDouble(4);
                成品N80套管接箍本期收入 = Reader.GetDouble(5);
                自产油管接箍本期收入 = Reader.GetInt32(6);
                自产套管接箍本期收入 = Reader.GetInt32(7);
            }
            Reader.Close();
            //分厂本期发出        
            string sql2 = @"
            declare @yc smalldatetime
            declare @ym smalldatetime           
            declare @y2yougc float
            declare @y5yougc float
            declare @y1taogc float
            declare @y2taogc float
            declare @y3youjg float
            declare @y3taojg float
            declare @y1zctjg float
            declare @y2zctjg int
            declare @y1zcyjg int
            declare @y2zcyjg int
            declare @y1zcbhq float
            declare @y2zcbhq float

            select @yc=起始日期,@ym=截止日期 from 生产月设置 where  分厂=0 and 年度='@年度' and 月份='@月份'
            select @y2yougc=isnull(round(SUM(重量),3),0) from 原料入出库  where 原料ID=1 and 单位ID=2 and 重量<0 and (日期 between @yc and @ym)
            select @y5yougc=isnull(round(SUM(重量),3),0) from 原料入出库  where 原料ID=1 and 单位ID=5 and 重量<0 and (日期 between @yc and @ym)
            select @y1taogc=isnull(round(SUM(重量),3),0) from 原料入出库  where 原料ID=2 and 单位ID=2 and 重量<0 and (日期 between @yc and @ym)
            select @y2taogc=isnull(round(SUM(重量),3),0) from 原料入出库  where 原料ID=2 and 单位ID=5 and 重量<0 and (日期 between @yc and @ym)
            select @y3youjg=isnull(round(SUM(重量),3),0) from 原料入出库  where 原料ID=3 and 单位ID=3 and 重量<0 and (日期 between @yc and @ym)
            select @y3taojg=isnull(round(SUM(重量),3),0) from 原料入出库  where 原料ID=4 and 单位ID=3 and 重量<0 and (日期 between @yc and @ym)

            select @y1zctjg=isnull(round(SUM(支数合计),3),0) from 自产接箍判定单  where 产品类别ID=2 and 生产单位ID=1 and 支数合计<0 and (日期 between @yc and @ym)
            select @y2zctjg=isnull(round(SUM(支数合计),3),0) from 自产接箍判定单  where 产品类别ID=2 and 生产单位ID=2 and 支数合计<0 and (日期 between @yc and @ym)
            select @y1zcyjg=isnull(round(SUM(支数合计),3),0) from 自产接箍判定单  where 产品类别ID=1 and 生产单位ID=1 and 支数合计<0 and (日期 between @yc and @ym)
            select @y2zcyjg=isnull(round(SUM(支数合计),3),0) from 自产接箍判定单  where 产品类别ID=1 and 生产单位ID=2 and 支数合计<0 and (日期 between @yc and @ym)

            select @y1zcbhq=isnull(round(SUM(套数),3),0) from 自产保护器判定单  where 产品类别ID=2 and 生产单位ID=1 and 套数<0 and (日期 between @yc and @ym)
            select @y2zcbhq=isnull(round(SUM(套数),3),0) from 自产保护器判定单  where 产品类别ID=2 and 生产单位ID=2 and 套数<0 and (日期 between @yc and @ym)

            select @y2yougc,@y5yougc,@y1taogc,@y2taogc,@y3youjg,@y3taojg,@y1zctjg,@y2zctjg,@y1zcyjg,@y2zcyjg,@y1zcbhq,@y2zcbhq";
            sql2 = sql2.Replace("@年度", this.Year0.SelectedValue);
            sql2 = sql2.Replace("@月份", this.Month0.SelectedValue);
            Cmd.CommandText = sql2;
            Reader = Cmd.ExecuteReader();
            if (Reader.Read())
            {
                二分厂油管料本期发出 = Reader.GetDouble(0);
                五分厂油管料本期发出 = Reader.GetDouble(1);
                一分厂套管料本期发出 = Reader.GetDouble(2);
                二分厂套管料本期发出 = Reader.GetDouble(3);
                三分厂油管接箍料本期发出 = Reader.GetDouble(4);
                三分厂套管接箍料本期发出 = Reader.GetDouble(5);
                一分厂自产套管接箍本期发出 = Reader.GetDouble(6);
                二分厂自产套管接箍本期发出 = Reader.GetInt32(7);

                一分厂自产油管接箍本期发出 = Reader.GetInt32(8);
                二分厂自产油管接箍本期发出 = Reader.GetInt32(9);
                一分厂自产保护器本期发出 = Reader.GetDouble(10);
                二分厂自产保护器本期发出 = Reader.GetDouble(11);

            }
            Reader.Close();

            //本期发出
            string sql3 = @"            
            select dbo.原料本期发出(@年度,@月份,1),dbo.原料本期发出(@年度,@月份,2),dbo.原料本期发出(@年度,@月份,3),dbo.原料本期发出(@年度,@月份,4),dbo.原料本期发出(@年度,@月份,6),dbo.原料本期发出(@年度,@月份,106),dbo.自产接箍本期发出(@年度,@月份,1),dbo.自产接箍本期发出(@年度,@月份,2)";
            sql3 = sql3.Replace("@年度", this.Year0.SelectedValue);
            sql3 = sql3.Replace("@月份", this.Month0.SelectedValue);
            Cmd.CommandText = sql3;
            Reader = Cmd.ExecuteReader();
            if (Reader.Read())
            {
                油管光管本期发出 = Reader.GetDouble(0);
                套管光管本期发出 = Reader.GetDouble(1);
                油管接箍光管本期发出 = Reader.GetDouble(2);
                套管接箍光管本期发出 = Reader.GetDouble(3);
                成品套管接箍本期发出 = Reader.GetDouble(4);
                成品N80套管接箍本期发出 = Reader.GetDouble(5);
                自产油管接箍本期发出 = Reader.GetInt32(6);
                自产套管接箍本期发出 = Reader.GetInt32(7);
            }
            Reader.Close();
            //本期结存
            string sql4 = @"            
            select dbo.原料期末库存(@年度,@月份,1),dbo.原料期末库存(@年度,@月份,2),dbo.原料期末库存(@年度,@月份,3),dbo.原料期末库存(@年度,@月份,4),dbo.原料期末库存(@年度,@月份,6),dbo.自产接箍期末库存(@年度,@月份,1),dbo.自产接箍期末库存(@年度,@月份,2)";
            sql4 = sql4.Replace("@年度", this.Year0.SelectedValue);
            sql4 = sql4.Replace("@月份", this.Month0.SelectedValue);
            Cmd.CommandText = sql4;
            Reader = Cmd.ExecuteReader();
            if (Reader.Read())
            {
                油管料本期结存 = Reader.GetDouble(0);
                套管料本期结存 = Reader.GetDouble(1);
                油管接箍料本期结存 = Reader.GetDouble(2);
                套管接箍料本期结存 = Reader.GetDouble(3);
                成品套管接箍本期结存 = Reader.GetDouble(4);    
                自产油管接箍本期结存 = Reader.GetInt32(5);
                自产套管接箍本期结存 = Reader.GetInt32(6);
            }
            Reader.Close();
            //材料
            //聚乙烯
            string sql5 = @"            
            select dbo.材料期初库存(@年度,@月份,0301000007) as 期初库存,dbo.材料本期收入(@年度,@月份,0301000007) as 本期收入,dbo.材料本期发出(@年度,@月份,0301000007) as 本期发出,dbo.材料期末库存(@年度,@月份,0301000007) as 期末库存 ";
            sql5 = sql5.Replace("@年度", Year0.SelectedValue);
            sql5 = sql5.Replace("@月份", Month0.SelectedValue);
            Cmd.CommandText = sql5;
            Reader = Cmd.ExecuteReader();
            if (Reader.Read())
            {
                聚乙烯期初库存 = Reader.GetDouble(0);
                聚乙烯本期入库 = Reader.GetDouble(1);
                聚乙烯本期出库 = Reader.GetDouble(2);
                聚乙烯期末库存 = Reader.GetDouble(3);
            }
            Reader.Close();
            //钢带
            string sql6 = @"            
            select dbo.材料期初库存(@年度,@月份,0401000002) as 期初库存,dbo.材料本期收入(@年度,@月份,0401000002) as 本期收入,dbo.材料本期发出(@年度,@月份,0401000002) as 本期发出,dbo.材料期末库存(@年度,@月份,0401000002) as 期末库存";
            sql6 = sql6.Replace("@年度", Year0.SelectedValue);
            sql6 = sql6.Replace("@月份", Month0.SelectedValue);
            Cmd.CommandText = sql6;
            Reader = Cmd.ExecuteReader();
            if (Reader.Read())
            {
                钢带期初库存 = Reader.GetDouble(0);
                钢带本期入库 = Reader.GetDouble(1);
                钢带本期出库 = Reader.GetDouble(2);
                钢带期末库存 = Reader.GetDouble(3);
            }
            Reader.Close();
            //套管螺纹保护环
            string sql7 = @"            
            select dbo.材料期初库存(@年度,@月份,0700000002) as 期初库存,dbo.材料本期收入(@年度,@月份,0700000002) as 本期收入,dbo.材料本期发出(@年度,@月份,0700000002) as 本期发出,dbo.材料期末库存(@年度,@月份,0700000002) as 期末库存";
            sql7 = sql7.Replace("@年度", Year0.SelectedValue);
            sql7 = sql7.Replace("@月份", Month0.SelectedValue);
            Cmd.CommandText = sql7;
            Reader = Cmd.ExecuteReader();
            if (Reader.Read())
            {
                套管螺纹保护环期初库存 = Reader.GetDouble(0);
                套管螺纹保护环本期入库 = Reader.GetDouble(1);
                套管螺纹保护环本期出库 = Reader.GetDouble(2);
                套管螺纹保护环期末库存 = Reader.GetDouble(3);
            }
            Reader.Close();
            //油管螺纹保护环
            string sql8 = @"            
            select dbo.材料期初库存(@年度,@月份,0700000001) as 期初库存,dbo.材料本期收入(@年度,@月份,0700000001) as 本期收入,dbo.材料本期发出(@年度,@月份,0700000001) as 本期发出,dbo.材料期末库存(@年度,@月份,0700000001) as 期末库存";
            sql8 = sql8.Replace("@年度", Year0.SelectedValue);
            sql8 = sql8.Replace("@月份", Month0.SelectedValue);
            Cmd.CommandText = sql8;
            Reader = Cmd.ExecuteReader();
            if (Reader.Read())
            {
                油管螺纹保护环期初库存 = Reader.GetDouble(0);
                油管螺纹保护环本期入库 = Reader.GetDouble(1);
                油管螺纹保护环本期出库 = Reader.GetDouble(2);
                油管螺纹保护环期末库存 = Reader.GetDouble(3);
            }
            Reader.Close();
            //自产螺纹保护环
            string sql9= @"            
            select dbo.自产保护器期初库存(@年度,@月份,2) as 期初库存,dbo.自产保护器本期收入(@年度,@月份,2) as 本期收入,dbo.自产保护器本期发出(@年度,@月份,2) as 本期发出,dbo.自产保护器期末库存(@年度,@月份,2)";
            sql9 = sql9.Replace("@年度", Year0.SelectedValue);
            sql9 = sql9.Replace("@月份", Month0.SelectedValue);
            Cmd.CommandText = sql9;
            Reader = Cmd.ExecuteReader();
            if (Reader.Read())
            {
                自产螺纹保护环期初库存 = Reader.GetDouble(0);
                自产螺纹保护环本期入库 = Reader.GetDouble(1);
                自产螺纹保护环本期出库 = Reader.GetDouble(2);
                自产螺纹保护环期末库存 = Reader.GetDouble(3);
            }
            Reader.Close();
            //待处理
            string sql10 = @"        
            select dbo.待处理本期发出(@年度,@月份,1),dbo.待处理本期发出(@年度,@月份,2)
                  ,dbo.待处理期末库存(@年度,@月份,1),dbo.待处理期末库存(@年度,@月份,2)
                  ,dbo.待处理分厂本期发出(@年度,@月份,2,1),dbo.待处理分厂本期发出(@年度,@月份,2,2)
                  ,dbo.待处理分厂本期发出(@年度,@月份,1,2),dbo.待处理分厂本期发出(@年度,@月份,1,5)";
            sql10 = sql10.Replace("@年度", this.Year0.SelectedValue);
            sql10 = sql10.Replace("@月份", this.Month0.SelectedValue);
            Cmd.CommandText = sql10;
            Reader = Cmd.ExecuteReader();
            if (Reader.Read())
            {
                待处理油管本期发出 = Reader.GetDouble(0);
                待处理套管本期发出 = Reader.GetDouble(1);
                待处理油管期末库存 = Reader.GetDouble(2);
                待处理套管期末库存 = Reader.GetDouble(3);
                一分厂待处理套管本期发出 = Reader.GetDouble(4);
                二分厂待处理套管本期发出 = Reader.GetDouble(5);
                二分厂待处理油管本期发出 = Reader.GetDouble(6);
                五分厂待处理油管本期发出 = Reader.GetDouble(7);
            }
            Reader.Close();
            //－－－－－－－－－－成品
            //二分厂月油管量          
             string sql11= @"
            declare @yc smalldatetime
            declare @ym smalldatetime
            declare @y2you float
            declare @y2youc float
            declare @y2n80you float
            declare @y2n80youc float
            select @yc=起始日期,@ym=截止日期 from 生产月设置 where  分厂=0 and 年度='@年度' and 月份='@月份' 
            select @y2you=isnull(sum(重量),0)/1000 from 产品库存 where 生产线 between 200 and 299 and 产品类别ID=1 and 入库日期 between @yc and @ym 
            select @y2youc=isnull(sum(重量),0)/1000 from 产品库存 where 生产线 between 200 and 299 and 产品类别ID=1 and 出库日期 between @yc and @ym 
            select @y2n80you=isnull(sum(重量),0)/1000 from 产品库存 where 生产线 between 200 and 299 and 产品类别ID=3 and 入库日期 between @yc and @ym 
            select @y2n80youc=isnull(sum(重量),0)/1000 from 产品库存 where 生产线 between 200 and 299 and 产品类别ID=3 and 出库日期 between @yc and @ym 
            select @y2you,@y2youc,@y2n80you,@y2n80youc";
            sql11 = sql11.Replace("@年度", this.Year0.SelectedValue);
            sql11 = sql11.Replace("@月份", this.Month0.SelectedValue);
            Cmd.CommandText=sql11; 
            Reader = Cmd.ExecuteReader();
            if (Reader.Read())
            {
                y2you = Reader.GetDouble(0) ;
                y2youc =Reader.GetDouble(1) ;
                y2youN80 = Reader.GetDouble(2) ;
                y2youcN80 = Reader.GetDouble(3) ;
            }
            Reader.Close();
            //五分厂月油管
            string sql12 = @"
            declare @yc smalldatetime
            declare @ym smalldatetime
            declare @y5you float
            declare @y5youc float
            declare @y5n80you float
            declare @y5n80youc float
            select @yc=起始日期,@ym=截止日期 from 生产月设置 where  分厂=0 and 年度='@年度' and 月份='@月份' 
            select @y5you=isnull(sum(重量),0)/1000 from 产品库存 where 生产线 between 500 and 599 and 产品类别ID=1 and 入库日期 between @yc and @ym 
            select @y5youc=isnull(sum(重量),0)/1000 from 产品库存 where 生产线 between 500 and 599 and 产品类别ID=1 and 出库日期 between @yc and @ym 
            select @y5n80you=isnull(sum(重量),0)/1000 from 产品库存 where 生产线 between 500 and 599 and 产品类别ID=3 and 入库日期 between @yc and @ym 
            select @y5n80youc=isnull(sum(重量),0)/1000 from 产品库存 where 生产线 between 500 and 599 and 产品类别ID=3 and 出库日期 between @yc and @ym 
            select @y5you,@y5youc,@y5n80you,@y5n80youc";
            sql12 = sql12.Replace("@年度", this.Year0.SelectedValue);
            sql12 = sql12.Replace("@月份", this.Month0.SelectedValue);
            Cmd.CommandText = sql12; 
            Reader = Cmd.ExecuteReader();
            if (Reader.Read())
            {
                y5you = Reader.GetDouble(0);
                y5youc = Reader.GetDouble(1);
                y5youN80 = Reader.GetDouble(2);
                y5youcN80 = Reader.GetDouble(3); 
            }
            Reader.Close();
            //一分厂月套管量
            string sql13= @"
                        declare @yc smalldatetime
                        declare @ym smalldatetime
                        declare @y1tao float
                        declare @y1taoc float
                        declare @y1n80tao float
                        declare @y1n80taoc float
                        select @yc=起始日期,@ym=截止日期 from 生产月设置 where  分厂=0 and 年度='@年度' and 月份='@月份' 
            select @y1tao=isnull(sum(重量),0)/1000 from 产品库存 where 生产线 between 100  and 199 and 产品类别ID=2 and 入库日期 between @yc and @ym 
            select @y1taoc=isnull(sum(重量),0)/1000 from 产品库存 where 生产线 between 100  and 199 and 产品类别ID=2 and 出库日期 between @yc and @ym 
            select @y1n80tao=isnull(sum(重量),0)/1000 from 产品库存 where 生产线 between 100  and 199 and 产品类别ID=4 and 入库日期 between @yc and @ym 
            select @y1n80taoc=isnull(sum(重量),0)/1000 from 产品库存 where 生产线 between 100  and 199 and 产品类别ID=4 and 出库日期 between @yc and @ym 
            select @y1tao,@y1taoc,@y1n80tao,@y1n80taoc";
            sql13 = sql13.Replace("@年度", this.Year0.SelectedValue);
            sql13 = sql13.Replace("@月份", this.Month0.SelectedValue);
            Cmd.CommandText = sql13; 
            Reader = Cmd.ExecuteReader();
            if (Reader.Read())
            {
                y1tao = Reader.GetDouble(0);
                y1taoc =Reader.GetDouble(1);
                y1taoN80 = Reader.GetDouble(2);
                y1taocN80 =Reader.GetDouble(3);
            }
            Reader.Close();
            //二分厂月套管量
            string sql14 = @"
                        declare @yc smalldatetime
                        declare @ym smalldatetime
                        declare @y2tao float
                        declare @y2taoc float
                        declare @y2n80tao float
                        declare @y2n80taoc float
                        select @yc=起始日期,@ym=截止日期 from 生产月设置 where  分厂=0 and  年度='@年度' and 月份='@月份' 
            select @y2tao=isnull(sum(重量),0)/1000 from 产品库存 where 生产线 between 200 and 299 and 产品类别ID=2 and 入库日期 between @yc and @ym 
            select @y2taoc=isnull(sum(重量),0)/1000 from 产品库存 where 生产线 between 200 and 299 and 产品类别ID=2 and 出库日期 between @yc and @ym 
            select @y2n80tao=isnull(sum(重量),0)/1000 from 产品库存 where 生产线 between 200 and 299 and 产品类别ID=4 and 入库日期 between @yc and @ym 
            select @y2n80taoc=isnull(sum(重量),0)/1000 from 产品库存 where 生产线 between 200 and 299 and 产品类别ID=4 and 出库日期 between @yc and @ym 
            select @y2tao,@y2taoc,@y2n80tao,@y2n80taoc";
            sql14 = sql14.Replace("@年度", this.Year0.SelectedValue);
            sql14 = sql14.Replace("@月份", this.Month0.SelectedValue);
            Cmd.CommandText = sql14; 
            Reader = Cmd.ExecuteReader();
            if (Reader.Read())
            {
                y2tao = Reader.GetDouble(0);
                y2taoc = Reader.GetDouble(1);
                y2taoN80 = Reader.GetDouble(2);
                y2taocN80 = Reader.GetDouble(3);
            }
            Reader.Close();
            //油套管本期结存  
            //本期结存
            string sql0 = @"select dbo.产品本期结存(@年度,@月份,1,0),dbo.产品本期结存(@年度,@月份,2,0),dbo.产品本期结存(@年度,@月份,3,0),dbo.产品本期结存(@年度,@月份,4,0)";
            sql0 = sql0.Replace("@年度", this.Year0.Text);
            sql0 = sql0.Replace("@月份", this.Month0.Text);
            Cmd.CommandText = sql0;
            Reader = Cmd.ExecuteReader();
            if (Reader.Read())
            {
                油管本期结存 = Reader.GetDouble(0);          
                套管本期结存 = Reader.GetDouble(1);
                油管N80本期结存 =Reader.GetDouble(2);
                套管N80本期结存 =Reader.GetDouble(3); 
            }
            Reader.Close();

            套管上期结存 = 套管本期结存 + (y1taoc + y2taoc) - (y1tao + y2tao);
            油管上期结存 = 油管本期结存 + (y2youc + y5youc) - (y2you + y5you);
            套管N80上期结存 = 套管N80本期结存 + (y1taocN80 + y2taocN80) - (y1taoN80 + y2taoN80);
            油管N80上期结存 = 油管N80本期结存 + (y2youcN80 + y5youcN80) - (y2youN80 + y5youN80);
          
       
            Reader.Dispose();
            Cmd.Dispose();
            Conn.Dispose();
        }
        protected void WriteToFile()
        {
            Response.ContentType = "application/ms-excel";
            Response.AddHeader("content-disposition", "attachment; filename=\"" + DateTime.Now.ToShortDateString() + ".xls\"");
            hssfworkbook.Write(Response.OutputStream);
        }

        protected void InitializeWorkbook()
        {

            FileStream file = new FileStream(HttpContext.Current.Server.MapPath("/Store/报表/模板/物资库存明细表.xls"), FileMode.Open, FileAccess.Read);

            hssfworkbook = new HSSFWorkbook(file);

            DocumentSummaryInformation dsi = PropertySetFactory.CreateDocumentSummaryInformation();
            dsi.Company = "物资库存明细表";
            hssfworkbook.DocumentSummaryInformation = dsi;

            SummaryInformation si = PropertySetFactory.CreateSummaryInformation();
            si.Subject = "报表";
            si.Title = "物资库存明细表";
            hssfworkbook.SummaryInformation = si;
        }
        protected void Button_Save_Click(object sender, EventArgs e)
        {
            InitializeWorkbook();
            HSSFSheet sheet1 = hssfworkbook.GetSheet("物资库存明细表");          
            Button_Query_Click(null, null);
            sheet1.GetRow(0).GetCell(0).SetCellValue(this.Year0.Text + "年" + this.Month0.Text + "月份物资库存明细表");

            sheet1.GetRow(6).GetCell(3).SetCellValue(套管光管期初库存);
            sheet1.GetRow(6).GetCell(9).SetCellValue(套管光管本期收入);
            sheet1.GetRow(6).GetCell(10).SetCellValue(System.Math.Abs(一分厂套管料本期发出));
            sheet1.GetRow(6).GetCell(11).SetCellValue(System.Math.Abs(二分厂套管料本期发出));
            sheet1.GetRow(6).GetCell(15).SetCellValue(System.Math.Abs(套管光管本期发出));
            sheet1.GetRow(6).GetCell(16).SetCellValue(套管料本期结存);

            sheet1.GetRow(7).GetCell(3).SetCellValue(待处理套管期末库存 - 套管接箍光管本期发出);
            sheet1.GetRow(7).GetCell(10).SetCellValue(0-一分厂待处理套管本期发出);
            sheet1.GetRow(7).GetCell(11).SetCellValue(0-二分厂待处理套管本期发出);
            sheet1.GetRow(7).GetCell(15).SetCellValue(0-待处理套管本期发出);
            sheet1.GetRow(7).GetCell(16).SetCellValue(待处理套管期末库存);

            sheet1.GetRow(8).GetCell(3).SetCellValue(套管接箍光管期初库存);
            sheet1.GetRow(8).GetCell(9).SetCellValue(套管接箍光管本期收入);
            sheet1.GetRow(8).GetCell(12).SetCellValue(System.Math.Abs(三分厂套管接箍料本期发出));
            sheet1.GetRow(8).GetCell(15).SetCellValue(System.Math.Abs(套管接箍光管本期发出));
            sheet1.GetRow(8).GetCell(16).SetCellValue(套管接箍料本期结存);

            sheet1.GetRow(9).GetCell(3).SetCellValue(油管光管期初库存);
            sheet1.GetRow(9).GetCell(9).SetCellValue(油管光管本期收入);
            sheet1.GetRow(9).GetCell(11).SetCellValue(System.Math.Abs(二分厂油管料本期发出));
            sheet1.GetRow(9).GetCell(14).SetCellValue(System.Math.Abs(五分厂油管料本期发出));
            sheet1.GetRow(9).GetCell(15).SetCellValue(System.Math.Abs(油管光管本期发出));
            sheet1.GetRow(9).GetCell(16).SetCellValue(油管料本期结存);

            sheet1.GetRow(10).GetCell(3).SetCellValue(待处理油管期末库存 - 待处理油管本期发出);
            sheet1.GetRow(10).GetCell(11).SetCellValue(0-二分厂待处理油管本期发出);
            sheet1.GetRow(10).GetCell(14).SetCellValue(0-五分厂待处理油管本期发出);
            sheet1.GetRow(10).GetCell(15).SetCellValue(0-待处理油管本期发出);
            sheet1.GetRow(10).GetCell(16).SetCellValue(待处理油管期末库存);

            sheet1.GetRow(11).GetCell(3).SetCellValue(油管接箍光管期初库存);
            sheet1.GetRow(11).GetCell(9).SetCellValue(油管接箍光管本期收入);
            sheet1.GetRow(11).GetCell(12).SetCellValue(System.Math.Abs(三分厂油管接箍料本期发出));
            sheet1.GetRow(11).GetCell(15).SetCellValue(System.Math.Abs(油管接箍光管本期发出));
            sheet1.GetRow(11).GetCell(16).SetCellValue(油管接箍料本期结存);

            sheet1.GetRow(12).GetCell(3).SetCellValue(聚乙烯期初库存);
            sheet1.GetRow(12).GetCell(9).SetCellValue(聚乙烯本期入库);
            sheet1.GetRow(12).GetCell(13).SetCellValue(System.Math.Abs(聚乙烯本期出库));
            sheet1.GetRow(12).GetCell(15).SetCellValue(System.Math.Abs(聚乙烯本期出库));
            sheet1.GetRow(12).GetCell(16).SetCellValue(聚乙烯期末库存);

            sheet1.GetRow(13).GetCell(3).SetCellValue(钢带期初库存);
            sheet1.GetRow(13).GetCell(9).SetCellValue(钢带本期入库);
            sheet1.GetRow(13).GetCell(13).SetCellValue(System.Math.Abs(钢带本期出库));
            sheet1.GetRow(13).GetCell(15).SetCellValue(System.Math.Abs(钢带本期出库));
            sheet1.GetRow(13).GetCell(16).SetCellValue(钢带期末库存);

            sheet1.GetRow(15).GetCell(3).SetCellValue(油管螺纹保护环期初库存);
            sheet1.GetRow(15).GetCell(9).SetCellValue(油管螺纹保护环本期入库);
            sheet1.GetRow(15).GetCell(11).SetCellValue(System.Math.Abs(油管螺纹保护环本期出库));
            sheet1.GetRow(15).GetCell(15).SetCellValue(System.Math.Abs(油管螺纹保护环本期出库));
            sheet1.GetRow(15).GetCell(16).SetCellValue(油管螺纹保护环期末库存);

            sheet1.GetRow(16).GetCell(3).SetCellValue(套管螺纹保护环期初库存);
            sheet1.GetRow(16).GetCell(9).SetCellValue(套管螺纹保护环本期入库);
            sheet1.GetRow(16).GetCell(11).SetCellValue(System.Math.Abs(油管螺纹保护环本期出库));
            sheet1.GetRow(16).GetCell(15).SetCellValue(System.Math.Abs(油管螺纹保护环本期出库));
            sheet1.GetRow(16).GetCell(16).SetCellValue(油管螺纹保护环期末库存);

            sheet1.GetRow(17).GetCell(3).SetCellValue(自产螺纹保护环期初库存);
            sheet1.GetRow(17).GetCell(9).SetCellValue(自产螺纹保护环本期入库);
            sheet1.GetRow(17).GetCell(10).SetCellValue(System.Math.Abs(一分厂自产保护器本期发出));
            sheet1.GetRow(17).GetCell(11).SetCellValue(System.Math.Abs(二分厂自产保护器本期发出));
            sheet1.GetRow(17).GetCell(15).SetCellValue(System.Math.Abs(自产螺纹保护环本期出库));
            sheet1.GetRow(17).GetCell(16).SetCellValue(自产螺纹保护环期末库存);

            sheet1.GetRow(19).GetCell(3).SetCellValue(成品接箍期初库存);
            sheet1.GetRow(19).GetCell(9).SetCellValue(成品套管接箍本期收入);
            sheet1.GetRow(19).GetCell(10).SetCellValue(System.Math.Abs(一分厂成品套管接箍本期发出));
            sheet1.GetRow(19).GetCell(11).SetCellValue(System.Math.Abs(二分厂成品套管接箍本期发出));
            sheet1.GetRow(19).GetCell(15).SetCellValue(System.Math.Abs((一分厂成品套管接箍本期发出 + 二分厂成品套管接箍本期发出)));
            sheet1.GetRow(19).GetCell(16).SetCellValue(成品接箍期初库存 + 成品套管接箍本期收入 + (一分厂成品套管接箍本期发出 + 二分厂成品套管接箍本期发出));

            sheet1.GetRow(20).GetCell(3).SetCellValue(成品N80接箍期初库存);
            sheet1.GetRow(20).GetCell(9).SetCellValue(成品N80套管接箍本期收入);
            sheet1.GetRow(20).GetCell(10).SetCellValue(System.Math.Abs(一分厂成品套管接箍本期发出));
            sheet1.GetRow(20).GetCell(11).SetCellValue(System.Math.Abs(二分厂成品套管接箍本期发出));
            sheet1.GetRow(20).GetCell(15).SetCellValue(成品N80套管接箍本期发出);
            sheet1.GetRow(20).GetCell(16).SetCellValue(成品接箍期初库存 + 成品套管接箍本期收入-成品N80套管接箍本期发出);

            sheet1.GetRow(21).GetCell(3).SetCellValue(自产套管接箍期初库存);
            sheet1.GetRow(21).GetCell(6).SetCellValue(自产套管接箍本期收入);
            sheet1.GetRow(21).GetCell(9).SetCellValue(自产套管接箍本期收入);
            sheet1.GetRow(21).GetCell(10).SetCellValue(System.Math.Abs(一分厂自产套管接箍本期发出));
            sheet1.GetRow(21).GetCell(11).SetCellValue(System.Math.Abs(二分厂自产套管接箍本期发出));
            sheet1.GetRow(21).GetCell(15).SetCellValue(System.Math.Abs(自产套管接箍本期发出));
            sheet1.GetRow(21).GetCell(16).SetCellValue(自产套管接箍本期结存);

            sheet1.GetRow(22).GetCell(3).SetCellValue(自产油管接箍期初库存);
            sheet1.GetRow(22).GetCell(6).SetCellValue(自产油管接箍本期收入);
            sheet1.GetRow(22).GetCell(9).SetCellValue(自产油管接箍本期收入);
            sheet1.GetRow(22).GetCell(10).SetCellValue(System.Math.Abs(一分厂自产油管接箍本期发出));
            sheet1.GetRow(22).GetCell(11).SetCellValue(System.Math.Abs(二分厂自产油管接箍本期发出));
            sheet1.GetRow(22).GetCell(15).SetCellValue(System.Math.Abs(自产油管接箍本期发出));
            sheet1.GetRow(22).GetCell(16).SetCellValue(自产油管接箍本期结存);

            sheet1.GetRow(24).GetCell(3).SetCellValue(套管上期结存);
            sheet1.GetRow(24).GetCell(4).SetCellValue(y1tao);
            sheet1.GetRow(24).GetCell(5).SetCellValue(y2tao);
            sheet1.GetRow(24).GetCell(9).SetCellValue((y1tao + y2tao));
            sheet1.GetRow(24).GetCell(10).SetCellValue(y1taoc);
            sheet1.GetRow(24).GetCell(11).SetCellValue(y2taoc);
            sheet1.GetRow(24).GetCell(15).SetCellValue((y1taoc + y2taoc));
            sheet1.GetRow(24).GetCell(16).SetCellValue(套管本期结存);
            sheet1.GetRow(25).GetCell(3).SetCellValue(油管上期结存);
            sheet1.GetRow(25).GetCell(5).SetCellValue(y2you);
            sheet1.GetRow(25).GetCell(8).SetCellValue(y5you);
            sheet1.GetRow(25).GetCell(9).SetCellValue((y2you + y5you));
            sheet1.GetRow(25).GetCell(11).SetCellValue(y2youc);
            sheet1.GetRow(25).GetCell(15).SetCellValue((y2youc + y5youc));
            sheet1.GetRow(25).GetCell(16).SetCellValue(油管本期结存);

            sheet1.GetRow(26).GetCell(3).SetCellValue(套管N80上期结存);
            sheet1.GetRow(26).GetCell(4).SetCellValue(y1taoN80);
            sheet1.GetRow(26).GetCell(5).SetCellValue(y2taoN80);
            sheet1.GetRow(26).GetCell(9).SetCellValue((y1taoN80 + y2taoN80));
            sheet1.GetRow(26).GetCell(10).SetCellValue(y1taocN80);
            sheet1.GetRow(26).GetCell(11).SetCellValue(y2taocN80);
            sheet1.GetRow(26).GetCell(15).SetCellValue((y1taocN80 + y2taocN80));
            sheet1.GetRow(26).GetCell(16).SetCellValue(套管本期结存);
            sheet1.GetRow(27).GetCell(3).SetCellValue(油管N80上期结存);
            sheet1.GetRow(27).GetCell(5).SetCellValue(y2youN80);
            sheet1.GetRow(27).GetCell(8).SetCellValue(y5youN80);
            sheet1.GetRow(27).GetCell(9).SetCellValue((y2youN80 + y5youN80));
            sheet1.GetRow(27).GetCell(11).SetCellValue(y2youcN80);
            sheet1.GetRow(27).GetCell(15).SetCellValue((y2youcN80 + y5youcN80));
            sheet1.GetRow(27).GetCell(16).SetCellValue(油管N80本期结存);
            WriteToFile();
            Response.End();
        }
    }
}
